package sios.modelo.dao.impl;

import com.softcomputo.commons.exception.DataAccessRuntimeException;
import com.softcomputo.dao.impl.AbstractJdbcDao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import sios.modelo.dao.QuerySQLServerDAO;

/**
 *
 * @author JGONZA7
 */
public class QuerySQLServerDAOImpl extends AbstractJdbcDao implements QuerySQLServerDAO {

    @Override
    public Map<Object, Object> consultarHistoria(String id) {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        Connection connection = null;
        try {
            connection = getConnection();
            pstm = connection.prepareStatement(
                    "SELECT rh.Id,rh.NumeroHistoria,rh.Caso,rh.CodigoRegistro,rh.Asunto,rh.FechaAsignacionRegistro,"
                    + " rh.Raiz,rh.Padre,rh.Usuario,rh.Prestador,rh.AdmPlan,rh.Protocolo,rh.Ciclo,rh.Diagnostico,"
                    + " rh.CAtencion,rh.Fluorosis ,rh.TipoFluorosis ,rh.RegistroXML,"
                    + " med.Nombre as NomMed,esp.Descrip as Especialidad,"
                    + " pct.Identificacion as Identificacion,"
                    + " pct.TipoID,pct.Nom1Afil"
                    + " FROM dbo.RegistrosHistoria as rh "
                    + " LEFT JOIN dbo.Pacientes pct on (pct.Id=rh.NumeroHistoria)"
                    + " LEFT JOIN dbo.Prestadores med on (med.Identificacion = rh.Prestador)"
                    + " LEFT JOIN dbo.Especialidades esp on (med.Especialidad = esp.Codigo)"
                    + " where rh.ID = ? order by rh.FechaAsignacionRegistro desc");
            pstm.setString(1, id);
            Map<Object, Object> map = new HashMap<Object, Object>();
            rs = pstm.executeQuery();
            int col = rs.getMetaData().getColumnCount();
            if (rs.next()) {
                for (int i = 1; i <= col; i++) {
                    String name_col = rs.getMetaData().getColumnName(i);
                    map.put(name_col, rs.getObject(i));
                }
            }

            return map;

        } catch (Exception e) {
            throw new DataAccessRuntimeException(e.getMessage(), e);
        } finally {
            this.closeResources(pstm, rs, connection);
        }
    }

    @Override
    public Integer cantidadHistoriasId(String id, String via_sios) {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        Integer cant = 0;
        Connection connection = null;
        try {
            connection = getConnection();
            pstm = connection.prepareStatement(
                            "SELECT count(rh.NumeroHistoria) as Total FROM dbo.RegistrosHistoria as rh "
                            + "LEFT JOIN dbo.Pacientes pct on (pct.Id=rh.NumeroHistoria) "
                            + "WHERE pct.Identificacion = ? AND rh.CodigoRegistro = ?;");

            pstm.setString(1, id);
            pstm.setString(2, via_sios);

            rs = pstm.executeQuery();
            if (rs.next()) {
                cant = rs.getInt("Total");
            }
            return cant;
        } catch (Exception e) {
            throw new DataAccessRuntimeException(e.getMessage(), e);
        } finally {
            this.closeResources(pstm, rs, connection);
        }
    }

    @Override
    public Integer cantidadHistoriasIdFecha(String id, String via_sios,
            Timestamp fecha)  {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        Integer cant = 0;
        Connection connection = null;
        try {
            connection = getConnection();
            pstm = connection.prepareStatement(
                            "SELECT count(rh.NumeroHistoria) as Total FROM dbo.RegistrosHistoria as rh "
                            + "LEFT JOIN dbo.Pacientes pct on (pct.Id=rh.NumeroHistoria) "
                            + "WHERE pct.Identificacion = ? AND rh.CodigoRegistro = ? AND rh.FechaAsignacionRegistro > ?;");
            pstm.setString(1, id);
            pstm.setString(2, via_sios);
            pstm.setTimestamp(3, fecha);

            rs = pstm.executeQuery();
            if (rs.next()) {
                // log.info(rs.getObject("Total"));
                cant = rs.getInt("Total");
            }
            return cant;
        } catch (Exception e) {
            throw new DataAccessRuntimeException(e.getMessage(), e);
        } finally {
            this.closeResources(pstm, rs, connection);
        }
    }

    @Override
    public List<Map<Object, Object>> listarHistorias(
            Map<String, Object> parametros)  {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        Connection connection = null;
        try {
            String parameter = (String) parametros.get("parameter");
            String value = (String) parametros.get("value");
            String identificacion = (String) parametros.get("identificacion");
            String padre = (String) parametros.get("padre");
            String tipo_historia = (String) parametros.get("tipo_historia");
            List<Map<Object, Object>> list = new ArrayList<Map<Object, Object>>();
            String sql;
            identificacion = value.substring(1, value.length() - 1);
            sql = "SELECT"
                    + " rh.Id,rh.NumeroHistoria,rh.Caso,rh.CodigoRegistro,rh.Asunto,rh.FechaAsignacionRegistro,"
                    + " rh.Raiz,rh.Padre,rh.Usuario,rh.Prestador,rh.AdmPlan,rh.Protocolo,rh.Ciclo,rh.Diagnostico,"
                    + " rh.CAtencion,rh.Fluorosis ,rh.TipoFluorosis ,rh.RegistroXML,"
                    + " med.Nombre as NomMed,esp.Descrip as Especialidad,"
                    + " pct.Identificacion as Identificacion,"
                    + " pct.TipoID,pct.Nom1Afil,pct.Sexo"
                    + " FROM dbo.RegistrosHistoria as rh "
                    + " LEFT JOIN dbo.Pacientes pct on (pct.Id=rh.NumeroHistoria)"
                    + " LEFT JOIN dbo.Prestadores med on (med.Identificacion = rh.Prestador)"
                    + " LEFT JOIN dbo.Especialidades esp on (med.Especialidad = esp.Codigo)"
                    + " WHERE "
                    + (!parameter.equalsIgnoreCase("Identificacion") ? " CONVERT(VARCHAR,"
                    + parameter + ",126) like " + value
                    : "")
                    + (identificacion != null && !identificacion.isEmpty() ? " pct.Identificacion = '"
                    + identificacion + "' and "
                    : "")
                    + "(not rh.RegistroXML is null)"
                    + (padre == null ? " and Padre = 'Null'" : " and Padre = '"
                    + padre + "'")
                    + (tipo_historia != null ? " and CodigoRegistro = '"
                    + tipo_historia + "'"
                    : " and rh.CodigoRegistro in ('01','02','07','08','40','41','35','36','38')")
                    + " order by rh.FechaAsignacionRegistro desc";
            connection = this.getConnection();
            pstm = connection.prepareStatement(sql);
            rs = pstm.executeQuery();
            int col = rs.getMetaData().getColumnCount();
            while (rs.next()) {
                Map<Object, Object> map = new HashMap<Object, Object>();
                for (int i = 1; i <= col; i++) {
                    String name_col = rs.getMetaData().getColumnName(i);
                    map.put(name_col, rs.getObject(i));
                }
                list.add(map);
            }
            return list;
        } catch (Exception e) {
            throw new DataAccessRuntimeException(e.getMessage(), e);
        } finally {
            this.closeResources(pstm, rs, connection);
        }
    }

}
